﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using ZLDC.Common.Estate;
using System.Data;

namespace ZLDC.DAL.Estate
{
    public class HousingAlertDAL
    {
                                public static void p_housingAlert_save(int Id, int housingId, string AlertType, string AlertScope, DateTime AlertDate,string AlertContent, int AddDeptId, string AddDept, int AddUserId, string AddUser, int dFlag, int dDeptId, string dDept, int dUserId, string dUser, int dltFlag, out string outmsg, out int newid, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();
            parms.Add(new SqlParameter("@Id", Id));
            parms.Add(new SqlParameter("@housingId", housingId));
            parms.Add(new SqlParameter("@AlertType", AlertType));
            parms.Add(new SqlParameter("@AlertScope", AlertScope));
            parms.Add(new SqlParameter("@AlertDate", AlertDate));
            parms.Add(new SqlParameter("@AlertContent", AlertContent));
            parms.Add(new SqlParameter("@AddDeptId", AddDeptId));
            parms.Add(new SqlParameter("@AddDept", AddDept));
            parms.Add(new SqlParameter("@AddUserId", AddUserId));
            parms.Add(new SqlParameter("@AddUser", AddUser));
            parms.Add(new SqlParameter("@dFlag", dFlag));
            parms.Add(new SqlParameter("@dDeptId", dDeptId));
            parms.Add(new SqlParameter("@dDept", dDept));
            parms.Add(new SqlParameter("@dUserId", dUserId));
            parms.Add(new SqlParameter("@dUser", dUser));
            parms.Add(new SqlParameter("@dltFlag", dltFlag));

            SqlParameter pOutmsg = new SqlParameter("@outmsg", SqlDbType.VarChar, 256);
            pOutmsg.Direction = ParameterDirection.Output;
            parms.Add(pOutmsg);

            SqlParameter pNewid = new SqlParameter("@newid", SqlDbType.Int, 4);
            pNewid.Direction = ParameterDirection.Output;
            parms.Add(pNewid);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);


            db.execute_procedure("p_housingAlert_save", parms);

            outmsg = (pOutmsg.Value == System.DBNull.Value) ? "" : Convert.ToString(pOutmsg.Value);
            newid = (pNewid.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pNewid.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

        }
                                public static DataTable p_housingAlert_list_table(int page, int pagesize, int housingId,int userid, ref int total, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@page", page));
            parms.Add(new SqlParameter("@pagesize", pagesize));
            parms.Add(new SqlParameter("@housingId", housingId));
            parms.Add(new SqlParameter("@userid", userid));

            SqlParameter pTotal = new SqlParameter("@total", SqlDbType.Int, 4);
            pTotal.Direction = ParameterDirection.InputOutput;
            pTotal.Value = total;
            parms.Add(pTotal);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);

            DataTable dt = null;
            dt = db.GetDataTable("p_housingAlert_list", parms);

            total = (pTotal.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pTotal.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

            return dt;
        }

        public static DataTable p_AlertByUserid(int page, int pagesize, int userid,int deptid, ref int total, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@page", page));
            parms.Add(new SqlParameter("@pagesize", pagesize));
            parms.Add(new SqlParameter("@adduserid", userid));
            parms.Add(new SqlParameter("@deptid", deptid));

            SqlParameter pTotal = new SqlParameter("@total", SqlDbType.Int, 4);
            pTotal.Direction = ParameterDirection.InputOutput;
            pTotal.Value = total;
            parms.Add(pTotal);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);

            DataTable dt = null;
            dt = db.GetDataTable("p_AlertInfo_list", parms);

            total = (pTotal.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pTotal.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

            return dt;
        }
                public static void p_housingAlert_update(int Id, int dFlag, int dDeptId, string dDept, int dUserId, string dUser, out int retval, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@Id", Id));
            parms.Add(new SqlParameter("@dFlag", dFlag));
            parms.Add(new SqlParameter("@dDeptId", dDeptId));
            parms.Add(new SqlParameter("@dDept", dDept));
            parms.Add(new SqlParameter("@dUserId", dUserId));
            parms.Add(new SqlParameter("@dUser", dUser));

            SqlParameter pOutmsg = new SqlParameter("@outmsg", SqlDbType.VarChar, 256);
            pOutmsg.Direction = ParameterDirection.Output;
            parms.Add(pOutmsg);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);


            db.execute_procedure("p_housingAlert_update", parms);

           
            retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

        }
                                                        public static void p_housingAlert_delete(int Id, out string outmsg, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@Id", Id));

            SqlParameter pOutmsg = new SqlParameter("@outmsg", SqlDbType.VarChar, 256);
            pOutmsg.Direction = ParameterDirection.Output;
            parms.Add(pOutmsg);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);


            db.execute_procedure("p_housingAlert_delete", parms);

            outmsg = (pOutmsg.Value == System.DBNull.Value) ? "" : Convert.ToString(pOutmsg.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

        }


                                                                                        public static DataTable GetAllAlert(int page, int pagesize, string conddtion, ref int total, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@page", page));
            parms.Add(new SqlParameter("@pagesize", pagesize));
            parms.Add(new SqlParameter("@condition", conddtion));

            SqlParameter pTotal = new SqlParameter("@total", SqlDbType.Int, 4);
            pTotal.Direction = ParameterDirection.InputOutput;
            pTotal.Value = total;
            parms.Add(pTotal);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);

            DataTable dt = null;
            dt = db.GetDataTable("p_v_alert_list", parms);

            total = (pTotal.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pTotal.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

            return dt;
        }

                                                public static DataTable GetWillToDatum(string condition, string subsidiary, _Database db)
        {
            DataTable dt = new DataTable();

            try
            {
                string strSql = "select id from t_housing ";
                strSql += condition;
                strSql += " and ((datediff(day,UDDate,getdate()) > (select top 1 itemValue-(select top 1 itemValue from dbo.t_system_option where item='当前盘待转资料盘提前提醒天数' and subsidiary in ('总公司', '" + subsidiary + "') order by ID desc) from dbo.t_system_option where item='当前盘转资料盘天数' and subsidiary in ('总公司', '" + subsidiary + "') order by ID desc) ";
                strSql += " and [use] in (1,4))";
                strSql += " or (datediff(day,UDDate,getdate()) > (select top 1 itemValue-(select top 1 itemValue from dbo.t_system_option where item='当前盘待转资料盘提前提醒天数' and subsidiary in ('总公司', '" + subsidiary + "') order by ID desc) from dbo.t_system_option where item='工商盘转资料盘天数' and subsidiary in ('总公司', '" + subsidiary + "') order by ID desc) ";
                strSql += " and [use] not in (1,4)))";

                DataSet ds = db.Query(strSql);
                if (ds.Tables.Count > 0)
                {
                    dt = ds.Tables[0];
                }
            }
            catch { }
            finally { db.close(); }
            
            return dt;
        }

                                                public static DataTable GetWillToDatum1(string condition, _Database db)
        {
            DataTable dt = new DataTable();

            try
            {
                string strSql = "select id from t_housing ";
                strSql += condition;
                strSql += " and  (mark4=1 or (tradeType=1 and totalprice <= 10) or (tradeType=2 and leaseprice=0)) ";
                DataSet ds = db.Query(strSql);
                if (ds.Tables.Count > 0)
                {
                    dt = ds.Tables[0];
                }
            }
            catch { }
            finally { db.close(); }

            return dt;
        }


                                                public static DataTable GetInformAlert(string condition, _Database db)
        {
            DataTable dt = new DataTable();

            try
            {
                string strSql = "select distinct(h.id) from t_housing h , t_housingInform i  ";
                strSql += condition.Replace("status", "h.status").Replace("dltflag", "h.dltflag");
                strSql += "  and h.id=i.housingId and i.isValid=0 and isnull(i.dresult,'') = '' and h.status='1'  and h.dltflag=0";

                DataSet ds = db.Query(strSql);
                if (ds.Tables.Count > 0)
                {
                    dt = ds.Tables[0];
                }
            }
            catch { }
            finally { db.close(); }

            return dt;
        }
                public static DataTable GetAlertList(int id,_Database db) 
        {
            DataTable dt = new DataTable();

            try
            {
                string sql = "select * from t_housingalert where id='" + id + "'";
                DataSet ds = db.Query(sql);
                if (ds.Tables.Count > 0)
                {
                    dt = ds.Tables[0];
                }
            }
            catch { }
            finally { db.close(); }

            return dt;
        }

    }
}
